package sen.queryUtils;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

/**
 * 新的查询辅助类
 * 自动生成条件语句再查询返回结果
 * @author Administrator
 */
public class HBQueryBean2 {

	/*******分页与排序的参数名---start********/
	private final static String COL = "col";
	private final static String ORDER = "order";
	private final static String ROWSTART = "rowStart";
	private final static String PAGESIZE = "pageSize";
	/*******分页与排序的参数名---end********/
	
	private Session session;
	private List<Cond> conds;
	
	
	/**
	 * (sql/countSql)与bean必须至少设置一个
	 */
	
	/**指定查询sql和统计countSql--start**/
	private String sql;
	private String countSql;
	/**指定查询sql和统计countSql--end**/
	
	/**
	 * 指定查询哪个bean
	 */
	private Class<?> bean;
	private String tableName;
	
	/***本类中设置值与使用的属性--start****/
	private String col;
	private String order;
	private int rowStart;
	private int pageSize;
	/***本类中设置值与使用的属性--end****/
	
	/**
	 * 用指定的bean进行hql查询
	 */
	public HBQueryBean2(Session session, List<Cond> conds, Class<?> bean) {
		super();
		this.session = session;
		this.conds = conds;
		this.bean = bean;
	}
	/**
	 * 用指定的sql与countSql进行查询，具体是本地sql查询还是hql查询，得看具体调用的方法
	 * @param session
	 * @param conds
	 * @param sql
	 * @param countSql
	 */
	public HBQueryBean2(Session session, List<Cond> conds, String sql,
			String countSql) {
		super();
		this.session = session;
		this.conds = conds;
		this.sql = sql;
		this.countSql = countSql;
	}
	/**
	 * 用指定table名进行本地sql查询
	 * @param session
	 * @param conds
	 * @param tableName
	 */
	public HBQueryBean2(Session session, List<Cond> conds,
			String tableName) {
		super();
		this.session = session;
		this.conds = conds;
		this.tableName = tableName;
	}
	/**
	 * 内部查询方法
	 * @param isHql:
	 * 	true	:用hql查询
	 *  false	:用native查询
	 * @return
	 */
	private Results query(boolean isHql) {
		
		if(MyUtils.isNull(sql)) {
			if(isHql) {
				if(MyUtils.isNull(bean)){
					throw new RuntimeException("如果没指定hql并且您调用的是queryHQL()的话，则必须指定bean。");
				}
				sql = "from " + bean.getName();
			} else {
				if(MyUtils.isNull(tableName)){
					throw new RuntimeException("如果没指定sql并且您调用的是queryNative()的话，则必须指定tableName。");
				}
				sql = "select * from " + tableName;
			}
		}
		String w = getWhereStr();
		boolean ifPaging = rowStart>=0 && pageSize>0;
		
		if(MyUtils.isNull(countSql) && ifPaging){
			if(isHql){
				countSql = " select count(*) from "+ bean.getName();
			} else {
				countSql = "select count(*) from "+ tableName;
			}
		}
		
		//这句得放在getWhereStr()之后，因为col,order的值在getWhereStr之后才被设置
		String orderStr = "";
		StringBuilder orderStrTemp = new StringBuilder();
		if(null!=col&&!"".equals(col) && null!=order&&!"".equals(order)){
			String[] cs = col.split(",");
			String[] os = order.split(",");
			for (int i = 0; i < os.length; i++) {
				orderStrTemp.append(cs[i] + " "+os[i] + ",");
			}
			MyUtils.delLast(orderStrTemp, ",", null, null);
			if(orderStrTemp.length()>0){
				orderStr = " order by " + orderStrTemp.toString();
			}
		}
		
		sql += w + orderStr;
		if(ifPaging){
			countSql += w;
		}
		
		Query query = null;
		if(isHql) {
			query = session.createQuery(sql);
		} else {
			query = session.createSQLQuery(sql);
		}
		if(ifPaging) {
			query.setFirstResult(rowStart);
			query.setMaxResults(pageSize);
		}
		
		Results results = new Results(query.list());
		if(ifPaging) {
			//查总记录数
			long total = 0;
			Query qCount = null;
			Object totalObj = null;
			if(isHql) {
				qCount = session.createQuery(countSql);
				totalObj = qCount.uniqueResult();
				if (totalObj!=null) {
					total = (Long)totalObj;
				}
			} else {
				qCount = session.createSQLQuery(countSql);
				
				totalObj = qCount.uniqueResult();
				if(totalObj!=null){
					try {
						total = Long.parseLong(totalObj + "");
					} catch (NumberFormatException e) {
					}
				}
			}
			results.setTotal(total);
			results.setRowStart(rowStart);
		}
		if(results.getTotal()==0) {
			results.setTotal(results.getDatas()!=null?results.getDatas().size():0);
		}
		return results;
	}
	
	/**
	 * 使用HQL查询
	 * @return
	 */
	public Results queryHQL() {
		return query(true);
	}
	/**
	 * 使用本地查询
	 * @return
	 */
	public Results queryNative() {
		return query(false);
	}
	private String getWhereStr() {
		if(conds==null || conds.isEmpty()) {
			return "";
		}
		StringBuilder w = new StringBuilder();
		boolean firstOr = true;
		for (Iterator<Cond> it = conds.iterator(); it.hasNext();) {
			Cond cond = it.next();
			
			if(cond instanceof Connector) {
				//如果当前不是左括号则删除最后的and
				if(!(((Connector) cond).getName().equals(Connector.LEFT_BRACKET.getName()))){
					delLastAnd(" and ", w);
				}
				Connector connector = (Connector)cond;
				w.append(" "+connector.getName()+" ");
				continue;
			}
			Conditional conditional = null;
			if(cond instanceof Conditional) {
				conditional = (Conditional)cond;
			}
			String name = conditional.getName();
			String value = conditional.getValue();
			int relator = conditional.getRelator();
			if(COL.equals(name)) {
				col = value;
				continue;
			} else if(ORDER.equals(name)) {
				order = value;
				continue;
			} else if(ROWSTART.equals(name)) {
				try {
					rowStart = Integer.parseInt(value);
				} catch (NumberFormatException e) {
				}
				continue;
			} else if(PAGESIZE.equals(name)) {
				try {
					pageSize = Integer.parseInt(value);
				} catch (NumberFormatException e) {
				}
				continue;
			}
			
			String value2 = "'"+value+"'";
			String item = "";
			switch (relator) {
			case Conditional.GT:
				item = name + ">" + value2;
				break;
			case Conditional.LT:
				item = name + "<" + value2;
				break;
			case Conditional.EQ:
				if(value == null) {
					item = name + " IS NULL";
				} else {
					item = name + "=" + value2;
				}
				break;
			case Conditional.GT_EQ:
				item = name + ">=" + value2;
				break;
			case Conditional.LT_EQ:
				item = name + "<=" + value2;
				break;
			case Conditional.START:
				item = name + " like '" + value + "%'";
				break;
			case Conditional.END:
				item = name + " like '%" + value + "'";
				break;
			case Conditional.DIM:
				item = name + " like '%" + value + "%'";
				break;
			case Conditional.L_DIM:
				item = name + " like '%" + value + "'";
				break;
			case Conditional.R_DIM:
				item = name + " like '" + value + "%'";
				break;
			case Conditional.SCOPE://如果是范围，则值应该是以逗号分隔
				String[] tmp = value.split(Conditional.SCOPE_SEPARATE);
				try {
					item = MyUtils.getOrStr(tmp, name, true);
				} catch (Exception e) {
				}
				break;
			case Conditional.NO_EQ:
				if(value == null) {
					item = name + " IS NOT NULL";
				} else {
					item = name + " !=" + value2;
				}
				break;
			case Conditional.ONE_OF_THEM:
				item = "(:n=':v' or :n like ':v,%' or :n like '%,:v,%' or :n like '%,:v')";
				item = item.replace(":n", name).replace(":v", value);
				break;
			}
			if(cond instanceof ConditionalOr) {
				if(firstOr) {
					w.append("(");
					firstOr = false;
				}
				w.append(item + " or ");
			} else {
				if(!firstOr) {//如果or关系的条件已经结束了，去掉末尾多余的or，加回括号
					w.delete(w.lastIndexOf("or"), w.length());
					w.append(") and ");
					firstOr = true;
				}
				w.append(item + " and ");
			}
		}
		if(!firstOr) {//如果一直没有and出现则表明全是or，去掉末尾多余的or，加回括号
			w.delete(w.lastIndexOf("or"), w.length());
			w.append(")");
			firstOr = true;
		}
		
		if(!"".equals(w.toString())) {
			delLastAnd(" and ", w);
			w.insert(0, " where ");
		}
		return w.toString();
	}
	
	private static boolean delLastAnd(String word, StringBuilder s){
		int index = s.lastIndexOf(word);
		if(index>0 && index==s.length()-word.length()) {
			s.delete(index, s.length());
		} else {
			return false;
		}
		return true;
	}
}